

**************************************************************************************
* create zip by time level dataset of house prices
**************************************************************************************

local file_path_in "/tochange/"

insheet using "`file_path_in'/house price index/Zip_Zhvi_AllHomes.csv", names clear

rename regionname zipcode

reshape long v, i(regionid zipcode city state metro) j(new)

gen year = 1996 if new==8

forval j = 1/20 {
	replace year = 1996+`j' if new==12*`j'+5
}

replace year  = year[_n-1] if mi(year)
bysort regionid year: gen month = _n
rename v houseprice

collapse houseprice, by(year month zipcode city state metro countyname)  

tostring year, replace
tostring month, replace
replace month = "0"+month if strlen(month)==1
gen day = "01"
gen record = year + month + day
gen temp = date(record,"YMD")
format temp %td
gen date = mofd(temp)
format date  %tm
drop temp
destring year, replace
destring month, replace

keep date zipcode houseprice state year month

save "`file_path_in'/ZillowHpZip.dta", replace
